﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public  class ES_InquireBLL

    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        

        public void Insert_hrm_es_inquires(int inquire_year,int emp_seq, int confirm, int created_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@inquire_year", inquire_year);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@confirm", confirm);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@created_by", created_by);
                // dbManager.AddParameters(5, "@created_when", DateTime.Today  );

                string strSQL;

                strSQL = " insert into hrm_es_inquires(inquire_year,emp_seq,record_status,created_by)"
                 + " values(@inquire_year,@emp_seq,@record_status,@created_by)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                

            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_hrm_es_move_requests}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        

        public void Insert_hrm_es_inquire_dtls(int inquire_seq,int inquire_subject_seq,int seq,int job_site_seq,string reason, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@inquire_seq", inquire_seq);
                dbManager.AddParameters(1, "@inquire_subject_seq", inquire_subject_seq);
                dbManager.AddParameters(2, "@seq", seq);
                dbManager.AddParameters(3, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(4, "@reason", reason);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@created_by", created_by);
                // dbManager.AddParameters(7, "@created_when", DateTime.Today  );


                string strSQL;

                strSQL = " insert into hrm_es_inquire_dtls(inquire_seq,inquire_subject_seq,seq,job_site_seq,reason,record_status,created_by)"
                 + " values(@inquire_seq,@inquire_subject_seq,@seq,@job_site_seq,@reason,@record_status,@created_by)";


                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
          

            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_hrm_es_move_requests}" + ex.Message);
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public void Update_hrm_es_inquire_dtls(int Sequence, int inquire_subject_seq, int no, int job_site_seq, string reason, int updated_by)

        {

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@inquire_subject_seq", inquire_subject_seq);
                dbManager.AddParameters(2, "@no", no);
                dbManager.AddParameters(3, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(4, "@reason", reason);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@updated_by", updated_by);
                dbManager.AddParameters(7, "@updated_when", DateTime.Today );

                string strSQL;
                strSQL = " update hrm_es_inquire_dtls "
                    + " set seq=@no ,"
                    + " inquire_subject_seq = @inquire_subject_seq,"
                    + " job_site_seq=@job_site_seq ,"
                    + " reason=@reason ,"
                    + " updated_by=@updated_by ,"
                    + " updated_when=@updated_when "
                    + " where inquire_dtls_seq = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);  

             }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();  
            }
        
        
        }

        //public string  chkMax(int subj_seq,int emp_seq,int inquire_subj_seq)

        //{
        //    try
        //    {
        //        int result;
        //        result = countMax(emp_seq, inquire_subj_seq); 
        //        dbManager.Open();
        //        string sql;
        //        DataSet ds = new DataSet(); 
        //        sql = "select *  from hrm_rc_inquire_subjects where subj_seq = " + subj_seq;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        if (ds.Tables[0].Rows.Count > 0)
        //        {
        //            int count_ = int.Parse(ds.Tables[0].Rows[0]["max"].ToString()) ;
        //            if (result >= count_)
        //            {
        //                return "ไม่สามารถเพิ่มได้ MAX";
        //            }
        //            else
        //            {
        //                return "";
        //            }
        //        }
        //    }

        //    catch(Exception ex) 
        //    {



        //    }
        //    finally
        //    {
        //        dbManager.Dispose();  
        //    }

        //    return "";
        
        
        //}


        //private int countMax(int emp_seq, int inquire_subject_seq)
        //{
        //    try
        //    {
        //        dbManager.Open();
        //        string sql;
        //        sql = " select count(*) as count_  from hrm_es_inquires inq,hrm_es_inquire_dtls dtl "
        //        + " where inq.inquire_seq = dtl.inquire_seq  "
        //        + " and inq.emp_seq = " + emp_seq
        //        + " and inquire_subject_seq = " + inquire_subject_seq;

        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0];
        //            if(dt.Rows.Count > 0)
        //            {
        //                return int.Parse(dt.Rows[0]["count_"].ToString()  );  
        //            }
        //            else
        //            {
        //                return 0;
        //            }
        //    }
        //    catch(Exception ex)
        //    {
        //        return 0;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();  
        //    }
                                
        //}


        public void Delete_Inquires(int Sequence, int UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@Sequence", Sequence);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today );

                string sql;
                sql = " update hrm_es_inquires set "
                + " record_status=@record_status"
                + ", updated_by = @updated_by"
                  + " where inquire_seq = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int seq,int UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@seq", seq);

                string sql;
                sql = " update hrm_es_inquire_dtls set "
                + " record_status=@record_status"
                + ", updated_by = @updated_by"
                  + " where inquire_dtls_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                
                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int chkDup(int emp_seq,int year)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@EmpSeq", emp_seq);
                dbManager.AddParameters(2, "@Year", year);

                string strSQL;
                strSQL = "SELECT *"
                    + " FROM HRM_ES_INQUIRES"
                    + " WHERE RECORD_STATUS not like @RecordStatus"
                        + " AND INQUIRE_YEAR = @Year"
                        + " AND EMP_SEQ = @EmpSeq";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return int.Parse(dt.Rows[0]["inquire_seq"].ToString());
                }
                else
                {
                    return 0;
                }

            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();  
            }
        }
        public int chkDupDtls(int emp_seq, int year, int JobSite)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                string strSQL;
                strSQL = "SELECT INQUIRE_DTLS_SEQ"
                    + " FROM HRM_ES_INQUIRE_DTLS"
                    + " WHERE INQUIRE_SEQ = "
                        + " AND RECORD_STATUS not like @RecordStatus";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return int.Parse(dt.Rows[0]["inquire_dtls_seq"].ToString());
                }
                else
                {
                    return 0;
                }

            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataSet Select(int emp_seq,int year)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@EmpSeq", emp_seq);
                dbManager.AddParameters(2, "@Year", year);

                string strSQL;
                strSQL = "SELECT i.*"
                        + ", d.INQUIRE_DTLS_SEQ, d.SEQ, d.REASON"
                        + ", d.INQUIRE_SUBJECT_SEQ, d.JOB_SITE_SEQ, j.JOB_SITE_NAME, s.DESCR"
                    + " FROM HRM_ES_INQUIRES i"
                    + " LEFT JOIN HRM_ES_INQUIRE_DTLS d ON i.INQUIRE_SEQ = d.INQUIRE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON d.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                        + " AND j.RECORD_STATUS not like @DeleteRecord"
                    + " LEFT JOIN HRM_RC_INQUIRE_SUBJECTS s ON d.INQUIRE_SUBJECT_SEQ = s.SUBJ_SEQ"
                        + " AND s.RECORD_STATUS not like @DeleteRecord"
                    + " WHERE i.RECORD_STATUS not like @DeleteRecord"
                        + " AND d.RECORD_STATUS not like @DeleteRecord"
                        + " AND i.INQUIRE_YEAR = @Year"
                        + " AND i.EMP_SEQ = @EmpSeq";

                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds;
            }
            catch (Exception ex)
            {

                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();  
            }
        }
        public DataSet SelectInquireDtls(int inquire_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@inquire_seq", inquire_seq);

                string strSQL;
                strSQL = "SELECT d.*"
                        + ", j.JOB_SITE_NAME, s.DESCR"
                    + " FROM HRM_ES_INQUIRE_DTLS d"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON d.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_RC_INQUIRE_SUBJECTS s ON d.SUBJ_SEQ = s.SUBJ_SEQ"
                    + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                        + " AND d.INQUIRE_SEQ = @inquire_seq";
                        
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch (Exception ex)
            {

                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetInquireSeq(int emp_seq, int year)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@year", year);

                string strSQL;
                strSQL = "select max(inquire_seq)"
                    + " from hrm_es_inquires "
                    + " where record_status not like @DeleteRecord"
                        + " and emp_seq = @emp_seq"
                        + " and inquire_year = @year";

                object obj;
                dbManager.Open();
                obj = dbManager.ExecuteScalar(CommandType.Text, strSQL);

                return (int)obj;

            }
            catch
            {
                return -1;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

   
    }
}
